Course: MySQL Training
Topic: CHECK Constraints and Their Behavior in MySQL
Course Link: MySQL Training Course
Demo Link: Register for a Free Course Demo
A CHECK constraint is a database rule that validates whether a value satisfies a specified condition before the database accepts an inserted or updated row. It helps maintain data accuracy and consistency by preventing values that do not meet defined business requirements.
For example, an employee's salary should not be negative, a student's marks should remain within an allowed range, and a product's discount percentage should not exceed a permitted limit. CHECK constraints allow these rules to be enforced directly in the database.
CHECK constraints are particularly useful because data may be inserted or updated through different applications, scripts, administrative tools, or database clients. Enforcing important validation rules at the database level provides an additional layer of protection.
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
age INT CHECK (age >= 18),
salary DECIMAL(10,2) CHECK (salary >= 0)
);In this example, the age must be at least 18, and the salary cannot be negative. If an inserted or updated row violates either enforced constraint, MySQL rejects the statement.
After studying this topic, you should be able to:
A CHECK constraint is a condition associated with a table or column. It specifies which values are considered valid according to a Boolean expression.
For example:
CHECK (price > 0)This condition requires the price to be greater than zero whenever the constraint is enforced and the row is inserted or updated.
A column-level CHECK constraint is written alongside a column definition. It is commonly used when the rule concerns one column.
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) CHECK (price > 0)
);The price must be greater than zero whenever it is supplied as a non-NULL value and the constraint is enforced.
A table-level CHECK constraint is declared separately from individual column definitions. It is useful for rules involving multiple columns.
CREATE TABLE bookings (
booking_id INT PRIMARY KEY,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
CONSTRAINT chk_booking_dates
CHECK (end_date >= start_date)
);The constraint ensures that the end date is not earlier than the start date.
MySQL version compatibility is important when working with CHECK constraints. MySQL versions before 8.0.16 generally parsed CHECK constraint syntax but did not enforce the conditions. Beginning with MySQL 8.0.16, CHECK constraints are supported and enforced by default unless explicitly declared otherwise.
| MySQL Version | CHECK Constraint Behavior |
|---|---|
| Before MySQL 8.0.16 | CHECK syntax was accepted in many cases, but the conditions were not enforced. |
| MySQL 8.0.16 and later | CHECK constraints are enforced by default unless declared NOT ENFORCED. |
| MySQL 8.0.16 and later with NOT ENFORCED | The constraint definition exists, but MySQL does not enforce its condition. |
Important: If a CHECK constraint appears to allow invalid values, verify the MySQL server version and whether the constraint is ENFORCED. Do not assume that every MySQL version handles CHECK constraints in the same way.
CREATE TABLE table_name (
column_name data_type CHECK (condition)
);Example:
CREATE TABLE students (
student_id INT PRIMARY KEY,
marks DECIMAL(5,2) CHECK (marks >= 0 AND marks <= 100)
);The marks must be between 0 and 100, inclusive, when the condition evaluates to TRUE.
CREATE TABLE table_name (
column1 data_type,
column2 data_type,
CONSTRAINT constraint_name CHECK (condition)
);Example:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
quantity INT NOT NULL,
unit_price DECIMAL(10,2) NOT NULL,
CONSTRAINT chk_order_quantity CHECK (quantity > 0),
CONSTRAINT chk_order_price CHECK (unit_price >= 0)
);Named constraints make it easier to identify rules in database definitions and error messages.
A CHECK constraint can be assigned a meaningful name using the CONSTRAINT keyword. Naming constraints is recommended for maintainability, troubleshooting, and schema changes.
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
age INT,
salary DECIMAL(10,2),
CONSTRAINT chk_employee_age CHECK (age >= 18),
CONSTRAINT chk_employee_salary CHECK (salary >= 0)
);In this example, the constraints are named chk_employee_age and chk_employee_salary.
chk_.When an INSERT statement attempts to create a row, MySQL evaluates applicable enforced CHECK constraints. If a condition evaluates to FALSE, the statement fails and the row is not inserted.
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
age INT,
salary DECIMAL(10,2),
CONSTRAINT chk_age CHECK (age >= 18),
CONSTRAINT chk_salary CHECK (salary >= 0)
);INSERT INTO employees
(employee_id, employee_name, age, salary)
VALUES (1, 'Amit', 25, 45000.00);This row satisfies both conditions: the age is at least 18 and the salary is nonnegative. The insert succeeds, assuming no other database rules are violated.
INSERT INTO employees
(employee_id, employee_name, age, salary)
VALUES (2, 'Riya', 16, 30000.00);This statement violates the age constraint. MySQL rejects the statement rather than inserting the row with an age of 16.
Another invalid example is:
INSERT INTO employees
(employee_id, employee_name, age, salary)
VALUES (3, 'Karan', 28, -5000.00);This statement violates the salary constraint because the salary is negative.
INSERT INTO employees
(employee_id, employee_name, age, salary)
VALUES
(4, 'Neha', 22, 35000.00),
(5, 'Rahul', 30, 52000.00);Both rows satisfy the CHECK conditions, so the statement can succeed if all other constraints are also satisfied. If a row violates an enforced CHECK constraint, the multi-row statement fails; do not assume MySQL will simply skip the invalid row.
CHECK constraints apply to UPDATE operations as well as INSERT operations. When an update produces a row that violates an enforced condition, MySQL rejects the update.
UPDATE employees
SET salary = 48000.00
WHERE employee_id = 1;The new salary is nonnegative, so it satisfies the salary constraint.
UPDATE employees
SET age = 15
WHERE employee_id = 1;This update violates chk_age, so MySQL rejects the statement.
UPDATE employees
SET age = 27,
salary = 50000.00
WHERE employee_id = 1;MySQL checks the resulting row against the applicable enforced CHECK constraints. Both values satisfy the example rules.
UPDATE employees
SET salary = -100
WHERE age > 25;If any targeted row would violate the salary constraint, the update statement fails. For transactional tables such as InnoDB, a failed statement normally does not leave a partial set of changes from that statement. Transaction-level behavior can also depend on the storage engine and transaction context.
CHECK constraints use SQL condition evaluation. A condition can evaluate to TRUE, FALSE, or UNKNOWN. In MySQL, an enforced CHECK constraint rejects a row when its condition evaluates to FALSE. A result of TRUE or UNKNOWN satisfies the CHECK constraint.
| Condition Result | CHECK Behavior |
|---|---|
| TRUE | The constraint is satisfied. |
| FALSE | The constraint is violated, and the statement is rejected. |
| UNKNOWN | The CHECK condition is considered satisfied. |
CREATE TABLE exam_results (
result_id INT PRIMARY KEY,
marks INT,
CONSTRAINT chk_marks
CHECK (marks >= 0 AND marks <= 100)
);Values such as 0, 50, and 100 satisfy the constraint. Values such as -1 and 101 violate it. A NULL marks value produces UNKNOWN for the range condition and therefore satisfies this CHECK constraint unless a separate NOT NULL requirement prevents it.
One of the most important details about CHECK constraints is their interaction with NULL. SQL uses three-valued logic, so a comparison involving NULL usually produces UNKNOWN rather than TRUE or FALSE.
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
price DECIMAL(10,2),
CONSTRAINT chk_product_price CHECK (price > 0)
);The constraint prevents zero or negative prices, but it does not by itself prohibit NULL. If the business rule requires every product to have a price, declare the column NOT NULL as well.
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL,
CONSTRAINT chk_product_price CHECK (price > 0)
);| Feature | Purpose | Example |
|---|---|---|
| CHECK | Validates a logical condition. | CHECK (price > 0) |
| NOT NULL | Prevents a column from containing NULL. | price DECIMAL(10,2) NOT NULL |
CREATE TABLE courses (
course_id INT PRIMARY KEY,
course_name VARCHAR(100) NOT NULL,
course_fee DECIMAL(10,2) NOT NULL,
CONSTRAINT chk_course_fee CHECK (course_fee >= 0)
);This table requires a course fee to be provided and ensures that the fee is not negative.
A CHECK condition can combine multiple expressions using logical operators such as AND, OR, and NOT. Parentheses improve readability and help prevent mistakes in complex conditions.
CREATE TABLE students (
student_id INT PRIMARY KEY,
age INT,
marks DECIMAL(5,2),
CONSTRAINT chk_student_values
CHECK (age >= 5 AND marks >= 0 AND marks <= 100)
);All the specified conditions must evaluate to TRUE for the combined expression to be TRUE.
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employment_type VARCHAR(20),
CONSTRAINT chk_employment_type
CHECK (
employment_type = 'Permanent'
OR employment_type = 'Contract'
)
);This condition allows the two listed employment types. Since the column is nullable, NULL can still satisfy the CHECK through an UNKNOWN result. Add NOT NULL if NULL must not be allowed.
CREATE TABLE ratings (
rating_id INT PRIMARY KEY,
rating_value INT,
CONSTRAINT chk_rating_value
CHECK (rating_value BETWEEN 1 AND 5)
);The rating must be between 1 and 5, inclusive. The BETWEEN operator is equivalent to checking that the value is greater than or equal to the lower limit and less than or equal to the upper limit.
CREATE TABLE support_tickets (
ticket_id INT PRIMARY KEY,
priority VARCHAR(20),
CONSTRAINT chk_ticket_priority
CHECK (priority IN ('Low', 'Medium', 'High'))
);The condition restricts non-NULL values to the listed priorities. Use NOT NULL if a priority must always be provided.
Table-level CHECK constraints are particularly useful when a valid value depends on the relationship between two or more columns.
CREATE TABLE projects (
project_id INT PRIMARY KEY,
project_name VARCHAR(100) NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
CONSTRAINT chk_project_dates
CHECK (end_date >= start_date)
);The end date cannot be earlier than the start date.
CREATE TABLE discount_rules (
rule_id INT PRIMARY KEY,
minimum_discount DECIMAL(5,2) NOT NULL,
maximum_discount DECIMAL(5,2) NOT NULL,
CONSTRAINT chk_discount_range
CHECK (
minimum_discount >= 0
AND maximum_discount <= 100
AND minimum_discount <= maximum_discount
)
);The constraint ensures that the minimum discount is nonnegative, the maximum discount does not exceed 100, and the minimum does not exceed the maximum.
CREATE TABLE order_items (
item_id INT PRIMARY KEY,
quantity INT NOT NULL,
unit_price DECIMAL(10,2) NOT NULL,
CONSTRAINT chk_item_quantity CHECK (quantity > 0),
CONSTRAINT chk_item_unit_price CHECK (unit_price >= 0)
);These constraints protect the table from zero or negative quantities and negative unit prices.
The ALTER TABLE statement can add a CHECK constraint after a table has already been created.
ALTER TABLE table_name
ADD CONSTRAINT constraint_name
CHECK (condition);CREATE TABLE inventory (
product_id INT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
quantity INT
);
ALTER TABLE inventory
ADD CONSTRAINT chk_inventory_quantity
CHECK (quantity >= 0);After the constraint is added, future INSERT and UPDATE operations must satisfy the condition. When adding an enforced CHECK constraint to a table that already contains rows, MySQL validates the existing data. If existing rows violate the condition, the operation can fail and the data must be corrected before the constraint can be added successfully.
ALTER TABLE inventory
ADD CONSTRAINT chk_inventory_quantity
CHECK (quantity >= 0);Add additional constraints through further ALTER TABLE statements as appropriate. Review existing data and test each rule before changing a production table.
MySQL provides metadata views that help identify defined CHECK constraints and determine whether they are enforced.
SHOW CREATE TABLE inventory;This statement displays the table definition, including CHECK constraints that are part of the schema.
SELECT
CONSTRAINT_SCHEMA,
CONSTRAINT_NAME,
CHECK_CLAUSE,
ENFORCED
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE();This query lists CHECK constraint metadata for the current database. The ENFORCED column indicates whether each constraint is enforced.
SELECT
tc.TABLE_NAME,
tc.CONSTRAINT_NAME,
cc.CHECK_CLAUSE,
cc.ENFORCED
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS AS cc
ON tc.CONSTRAINT_SCHEMA = cc.CONSTRAINT_SCHEMA
AND tc.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_SCHEMA = DATABASE()
AND tc.CONSTRAINT_TYPE = 'CHECK';This query helps associate constraint definitions with table names. For systems with similarly named constraints across different tables, include table metadata and schema filters appropriate to your environment when investigating results.
In supported MySQL versions, a CHECK constraint can be declared ENFORCED or NOT ENFORCED. Enforced constraints validate affected rows. A NOT ENFORCED constraint remains defined in the schema but does not reject rows that violate its condition.
CREATE TABLE products (
product_id INT PRIMARY KEY,
price DECIMAL(10,2),
CONSTRAINT chk_price CHECK (price > 0) ENFORCED
);This constraint is explicitly enforced. A zero or negative non-NULL price violates the condition.
CREATE TABLE product_staging (
product_id INT PRIMARY KEY,
price DECIMAL(10,2),
CONSTRAINT chk_staging_price
CHECK (price > 0) NOT ENFORCED
);The condition is recorded but is not used to reject invalid values. This can be useful for certain transitional or staging scenarios, but it should not be mistaken for data validation.
ALTER TABLE product_staging
ALTER CHECK chk_staging_price ENFORCED;Before enabling enforcement, check whether existing rows satisfy the condition. If existing data violates the condition, the operation can fail. Verify the supported syntax for the MySQL version in use before applying schema changes.
If a CHECK constraint is no longer required, it can be removed using ALTER TABLE and DROP CHECK.
ALTER TABLE table_name
DROP CHECK constraint_name;ALTER TABLE inventory
DROP CHECK chk_inventory_quantity;After the constraint is removed, MySQL no longer enforces that particular rule. Other constraints, such as NOT NULL, PRIMARY KEY, UNIQUE, and foreign key constraints, remain in place unless they are separately changed.
Best practice: Confirm why a constraint is being removed and whether application code or another database rule will continue to protect the data.
| Constraint | Purpose | Example |
|---|---|---|
| CHECK | Validates a logical condition. | CHECK (age >= 18) |
| NOT NULL | Prevents NULL values. | name VARCHAR(100) NOT NULL |
| UNIQUE | Prevents duplicate values in a unique key. | UNIQUE (email) |
| PRIMARY KEY | Identifies each row and requires a unique, non-NULL key. | PRIMARY KEY (employee_id) |
| FOREIGN KEY | Maintains referential integrity between related tables. | FOREIGN KEY (department_id) REFERENCES departments(department_id) |
These constraints serve different purposes and can be combined to establish stronger data integrity rules.
CHECK expressions should be written with the column's data type and expected values in mind. MySQL applies its normal expression evaluation and type-conversion rules, so implicit conversions can sometimes produce unexpected results.
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
experience_years INT NOT NULL,
CONSTRAINT chk_experience
CHECK (experience_years >= 0)
);CREATE TABLE payments (
payment_id INT PRIMARY KEY,
amount DECIMAL(10,2) NOT NULL,
CONSTRAINT chk_payment_amount
CHECK (amount >= 0)
);CREATE TABLE subscriptions (
subscription_id INT PRIMARY KEY,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
CONSTRAINT chk_subscription_dates
CHECK (end_date >= start_date)
);CREATE TABLE applications (
application_id INT PRIMARY KEY,
status VARCHAR(20) NOT NULL,
CONSTRAINT chk_application_status
CHECK (status IN ('Pending', 'Approved', 'Rejected'))
);For text conditions, consider case sensitivity, collation, and the exact allowed values. If a rule requires a particular format or more complex validation, confirm that the expression is supported and behaves as expected in the target MySQL version.
Possible cause: The MySQL version is older than 8.0.16, or the constraint was declared NOT ENFORCED.
Solution: Check the server version and inspect the constraint definition.
SELECT VERSION();
SHOW CREATE TABLE products;Possible cause: The table contains values that do not satisfy the condition being added.
Solution: Find and correct invalid records before adding the enforced constraint.
SELECT *
FROM inventory
WHERE quantity < 0;Possible cause: The CHECK expression evaluates to UNKNOWN when its column contains NULL.
Solution: Use NOT NULL if the column must always contain a value.
CREATE TABLE products (
product_id INT PRIMARY KEY,
price DECIMAL(10,2) NOT NULL,
CONSTRAINT chk_price CHECK (price > 0)
);Possible cause: The supplied values violate an enforced CHECK condition.
Solution: Review the constraint definition and correct the data rather than disabling the constraint without a valid reason.
Possible cause: The constraint name was misspelled, belongs to another table, or differs from the expected name.
Solution: Inspect the table definition and metadata before attempting to alter or drop the constraint.
SHOW CREATE TABLE inventory;
SELECT
CONSTRAINT_NAME,
CHECK_CLAUSE,
ENFORCED
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE();chk_salary_nonnegative.In this practical example, you will create an employee table with CHECK constraints to validate age, salary, and employment status.
CREATE TABLE employee_records (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
age INT NOT NULL,
salary DECIMAL(10,2) NOT NULL,
employment_status VARCHAR(20) NOT NULL,
CONSTRAINT chk_employee_age
CHECK (age >= 18 AND age <= 65),
CONSTRAINT chk_employee_salary
CHECK (salary >= 0),
CONSTRAINT chk_employee_status
CHECK (
employment_status IN (
'Active',
'On Leave',
'Resigned'
)
)
);INSERT INTO employee_records
(employee_id, employee_name, age, salary, employment_status)
VALUES
(1, 'Amit Sharma', 28, 45000.00, 'Active'),
(2, 'Priya Verma', 35, 60000.00, 'On Leave'),
(3, 'Rahul Mehta', 42, 72000.00, 'Resigned');These rows satisfy the example CHECK constraints.
INSERT INTO employee_records
(employee_id, employee_name, age, salary, employment_status)
VALUES (4, 'Neha Singh', 16, 30000.00, 'Active');The insert is rejected because the age is below the allowed minimum.
INSERT INTO employee_records
(employee_id, employee_name, age, salary, employment_status)
VALUES (5, 'Karan Patel', 30, -2000.00, 'Active');The insert is rejected because the salary is negative.
INSERT INTO employee_records
(employee_id, employee_name, age, salary, employment_status)
VALUES (6, 'Meera Shah', 29, 40000.00, 'Unknown');The insert is rejected because the employment status does not match one of the permitted values.
SELECT *
FROM employee_records
ORDER BY employee_id;Only records that satisfy the table's constraints should be present, assuming all statements were executed successfully and no other database behavior affected the operations.
Create a table named student_marks with the following columns:
student_id as the primary key.student_name as a required text field.marks as a required numeric field between 0 and 100.Create a named CHECK constraint that prevents marks outside the allowed range. Test values such as 0, 75, 100, -5, and 105.
Create a products table with a positive price and a nonnegative stock quantity. Use separate named CHECK constraints and test valid and invalid values.
Create a project_schedule table with start and end dates. Add a table-level CHECK constraint ensuring that the end date is not earlier than the start date.
Create a table without a CHECK constraint, insert several rows, and then use ALTER TABLE to add a rule. Test what happens when existing data violates the proposed rule.
Create a table with a nullable column and a CHECK constraint. Test NULL, valid values, and invalid values. Then add NOT NULL and compare the results.
A CHECK constraint validates a condition for a row and prevents the row from being inserted or updated when the enforced condition evaluates to FALSE.
MySQL began enforcing CHECK constraints in MySQL 8.0.16. Earlier versions generally parsed the syntax without enforcing the condition.
Yes. Logical operators such as AND and OR can combine multiple conditions in a CHECK expression.
Yes. A table-level CHECK constraint can validate relationships between columns in the same row, such as ensuring that an end date is not earlier than a start date.
No. A condition involving NULL can evaluate to UNKNOWN, which satisfies a CHECK constraint. Use NOT NULL if NULL must be prohibited.
MySQL rejects the statement when the condition evaluates to FALSE.
MySQL rejects the update when the resulting row violates an enforced CHECK condition.
CHECK validates a logical expression, while NOT NULL requires a column to contain a non-NULL value.
Use ALTER TABLE with ADD CONSTRAINT and CHECK, for example:
ALTER TABLE products
ADD CONSTRAINT chk_product_price
CHECK (price > 0);Use SHOW CREATE TABLE or query INFORMATION_SCHEMA.CHECK_CONSTRAINTS.
It means the constraint definition exists but MySQL does not enforce its condition when validating rows.
Use ALTER TABLE with DROP CHECK and the constraint's name.
ALTER TABLE products
DROP CHECK chk_product_price;No. They are valuable for database-level row validation, but application validation, foreign keys, unique constraints, triggers, and transaction logic may still be needed for other requirements.
Adding an enforced constraint can fail if existing rows violate its condition. Correct the data and retry the schema change.
CHECK constraints are an important MySQL feature for maintaining data integrity and enforcing business rules directly within the database. They help restrict invalid ages, negative prices, out-of-range marks, invalid statuses, and inconsistent date relationships.
To use CHECK constraints effectively, understand MySQL version compatibility, enforcement status, NULL behavior, and the distinction between row-level validation and other database rules. Test constraints carefully before applying them to production data, and use meaningful names to simplify maintenance and troubleshooting.
To continue learning database design, SQL queries, constraints, and practical MySQL development, explore the JustAcademy MySQL Training Course. You can also register for a course demo to learn more about the training.
